﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VswService_V7.ClientCommand;
using VswService_V7.Utility;

namespace VswService_V7.DataBase.Tables
{
    public class SHIFT_TABLE
    {
        public SHIFT_TABLE()
        {
            //ShiftTable = RefrushShiftTable();
        }

        #region public property

        public static readonly string tableName = "SHIFT_TABLE";
        public static readonly string fd_shiftName = "SHIFT_NAME";
        public static readonly string fd_startTime = "START_TIME";
        public static readonly string fd_endTime = "END_TIME";

        #endregion
        /// <summary>
        /// get all shift table data
        /// </summary>
        /// <returns></returns>
        public static DataTable GetShiftTable()
        {
            string selectShiftTableSQL = "select * from " + tableName;
            DataTable ShiftTable = OracleHelper.Exct_QueryTable(selectShiftTableSQL);

            if (ShiftTable == null)
                throw new Exception("There is no shift table!");
            else
                return ShiftTable;
        }
        /// <summary>
        /// replace the original shift table data
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public static DataTable ResetShiftTable(DataTable dataTable)
        {
            try
            {               
                using (OracleConnection conn = new OracleConnection(OracleHelper.oracleConnectionStringBuilder.ConnectionString))
                {
                    conn.Open();
                    using (OracleTransaction trans = conn.BeginTransaction())
                    {
                        try
                        {
                            string deleteSQL = "delete from " + tableName;
                            using (OracleCommand cmd = new OracleCommand(deleteSQL, conn))
                            {
                                cmd.CommandType = CommandType.Text;
                                int result = cmd.ExecuteNonQuery();
                                if (result <= 0)
                                {
                                    trans.Rollback();
                                    trans.Commit();
                                    return null;
                                }
                            }

                            StringBuilder insertsql = new StringBuilder();
                            insertsql.Append("insert all");
                            foreach (DataRow item in dataTable.Rows)
                            {
                                string s1 = string.Format(" into {0}({1},{2},{3})", tableName, fd_shiftName, fd_startTime, fd_endTime);
                                insertsql.Append(s1);
                                string s2 = string.Format("('{0}','{1}','{2}')",
                                    item[fd_shiftName].ToString(), item[fd_startTime].ToString(), item[fd_endTime]);
                                insertsql.Append(" values" + s2);
                            }
                            insertsql.Append(" select* from dual");
                            using (OracleCommand cmd = new OracleCommand(insertsql.ToString(), conn))
                            {
                                cmd.CommandType = CommandType.Text;
                                int result = cmd.ExecuteNonQuery();
                                if (result > 0)
                                {
                                    trans.Commit();
                                }
                                if (result <= 0)
                                {
                                    trans.Rollback();
                                    trans.Commit();
                                    return null;
                                }
                            }
                        }
                        catch
                        {
                            trans.Rollback();
                            trans.Commit();
                            return null;
                        }
                    }
                    string selectSQL = "select * from " + tableName;
                    return OracleHelper.Exct_QueryTable(selectSQL);
                }
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// get current shift table data by current time
        /// </summary>
        /// <param name="time"></param>
        /// <returns></returns>
        public static string GetCurrentShift(DateTime time)
        {
            string selectShiftTableSQL = "select * from " + tableName;
            DataTable ShiftTable = OracleHelper.Exct_QueryTable(selectShiftTableSQL);

            if (ShiftTable == null)
                throw new Exception("There is no shift table!");
            DateTime start = GetStartTime(ShiftTable);
            DateTime end = GetEndTime(ShiftTable);
            TimeSpan ts = time - start;
            double hours = ts.TotalHours;
            double shiftHours = (end - start).TotalHours;
            double leftHours = hours % shiftHours;
            DateTime current = start.AddHours(leftHours);
            string shift = "Unknow";
            foreach (DataRow row in ShiftTable.Rows)
            {
                int result = DateTime.Compare(current, Convert.ToDateTime(row[fd_endTime]));
                if (result < 0)
                {
                    shift = row[fd_shiftName].ToString();
                    //break是跳出一层循环，continue是结束一趟循环 ,return才是结束所有层循环! 
                    break;
                }
            }
            return shift;
        }
        /// <summary>
        /// get shift start time
        /// </summary>
        /// <param name="shift"></param>
        /// <returns></returns>
        public static DateTime GetStartTime(DataTable shift)
        {
            string fs = string.Format("Min({0})", fd_startTime);
            string startTime = (string)shift.Compute(fs, "true");
            return Convert.ToDateTime(startTime);
        }
        /// <summary>
        /// get shift end time
        /// </summary>
        /// <param name="shift"></param>
        /// <returns></returns>
        public static DateTime GetEndTime(DataTable shift)
        {
            string fe = string.Format("Max({0})", fd_endTime);
            string endTime = (string)shift.Compute(fe, "true");
            return Convert.ToDateTime(endTime);
        }

        public static double GetShiftTotalHours(DataTable shift)
        {
            return (GetEndTime(shift) - GetStartTime(shift)).TotalHours;
        }

        public static List<string> GetShiftNames()
        {
            string selectShiftTableSQL = "select * from " + tableName;
            DataTable shiftTable = OracleHelper.Exct_QueryTable(selectShiftTableSQL);
            if (shiftTable == null)
                throw new Exception("There is no shift table!");
            List<string> shiftNames = new List<string>();

            for (int i = 0; i < shiftTable.Rows.Count; i++)
            {
                shiftNames.Add(shiftTable.Rows[i][fd_shiftName].ToString());
            }
            return shiftNames;
        }

    }
}
